package com.scuec.groupfive.mapper;

import com.scuec.groupfive.vo.KnowledgeStatisticsVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 知识点掌握情况统计Mapper
 */
@Mapper
public interface KnowledgeStatisticsMapper {

    /**
     * 获取用户在特定科目和考试类型下的章节掌握情况统计
     * @param userId 用户ID
     * @param subjectId 科目ID
     * @param typeId 考试类型ID
     * @return 章节掌握情况统计列表
     */
    @Select("SELECT \n" +
            "    c.chapter_name AS chapterName,\n" +
            "    -- 做对的选择题和填空题唯一题数\n" +
            "    COUNT(DISTINCT IF(ar.is_correct = 1 AND q.question_type IN ('single_choice', 'fill_in_the_blank'), q.question_id, NULL)) AS correctQuestionCount,\n" +
            "    -- 做过的选择题和填空题唯一题数\n" +
            "    COUNT(DISTINCT IF(q.question_type IN ('single_choice', 'fill_in_the_blank'), q.question_id, NULL)) AS totalQuestionCount,\n" +
            "    -- 正确率计算（仅包含选择题和填空题）\n" +
            "    ROUND(\n" +
            "        IF(\n" +
            "            COUNT(DISTINCT IF(q.question_type IN ('single_choice', 'fill_in_the_blank'), q.question_id, NULL)) = 0, \n" +
            "            0, \n" +
            "            (COUNT(DISTINCT IF(ar.is_correct = 1 AND q.question_type IN ('single_choice', 'fill_in_the_blank'), q.question_id, NULL)) / \n" +
            "             COUNT(DISTINCT IF(q.question_type IN ('single_choice', 'fill_in_the_blank'), q.question_id, NULL))) * 100\n" +
            "        ), \n" +
            "        2\n" +
            "    ) AS correctRate,\n" +
            "    \n" +
            "    -- 进度计算（保持不变）\n" +
            "    COUNT(DISTINCT q.question_id) AS totalQuestionCount,\n" +
            "    (SELECT COUNT(DISTINCT q_all.question_id) \n" +
            "     FROM questions q_all \n" +
            "     WHERE q_all.chapter_id = c.chapter_id \n" +
            "       AND q_all.subject_id = #{subjectId} \n" +
            "       AND q_all.type_id = #{typeId}) AS chapterTotalQuestionCount,\n" +
            "    -- 进度百分比计算（保持不变）\n" +
            "    ROUND(\n" +
            "        IF(\n" +
            "            (SELECT COUNT(DISTINCT q_all.question_id) \n" +
            "             FROM questions q_all \n" +
            "             WHERE q_all.chapter_id = c.chapter_id \n" +
            "               AND q_all.subject_id = #{subjectId} \n" +
            "               AND q_all.type_id = #{typeId}) = 0, \n" +
            "            0, \n" +
            "            (COUNT(DISTINCT q.question_id) / \n" +
            "             (SELECT COUNT(DISTINCT q_all.question_id) \n" +
            "              FROM questions q_all \n" +
            "              WHERE q_all.chapter_id = c.chapter_id \n" +
            "                AND q_all.subject_id = #{subjectId} \n" +
            "                AND q_all.type_id = #{typeId})) * 100\n" +
            "        ), \n" +
            "        2\n" +
            "    ) AS progressRate\n" +
            "FROM chapters c \n" +
            "-- 左连接用户做题记录（仅筛选目标用户）\n" +
            "LEFT JOIN (\n" +
            "    SELECT ar.* \n" +
            "    FROM answer_records ar \n" +
            "    WHERE ar.user_id = #{userId}\n" +
            ") ar ON c.chapter_id = (\n" +
            "    -- 通过题目表获取章节ID\n" +
            "    SELECT q_chapter.chapter_id \n" +
            "    FROM questions q_chapter \n" +
            "    WHERE q_chapter.question_id = ar.question_id\n" +
            ")\n" +
            "-- 左连接题目表（筛选高等数学且题型为选择题/填空题）\n" +
            "LEFT JOIN questions q ON ar.question_id = q.question_id \n" +
            "    AND q.subject_id = #{subjectId} \n" +
            "    AND q.type_id = #{typeId}\n" +
            "WHERE c.subject_id = #{subjectId} -- 过滤高等数学章节\n" +
            "GROUP BY c.chapter_id, c.chapter_name\n" +
            "ORDER BY c.chapter_id ASC;")
    List<KnowledgeStatisticsVO> getKnowledgeStatistics(@Param("userId") Long userId, @Param("subjectId") Integer subjectId, @Param("typeId") Integer typeId);
}